﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using Citi.Helpers;
using Citi.RoutineBase;

namespace Citi.FinancePlanningAnalysis.SchedulerUi
{
    public abstract class GeneralImport : IExecutableRoutine
    {
        public IEnumerable<RoutineBase.RoutineParameter> Parameters
        {
            get { return null; }
        }

        private string DatabaseConnectionString { get { return ConfigurationManager.AppSettings["DatabaseConnectionString"]; } }
        private string ExcelConnectionString { get { return ConfigurationManager.AppSettings["ExcelConnectionString"]; } }
        public abstract string ExcelPath { get; }
        public abstract List<string> Files { get; set; }
        public abstract string QueryMergeDate { get; }
        public abstract string QueryDeletePrevious { get;  }
        public abstract string QueryExcel { get;  }
        public abstract string TableTemp { get;  }
        public abstract Dictionary<string, string> ExcelColumnMappings { get;  }
        public abstract string SheetName { get; }

        public void Rollback(ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {

        }

        public void Start(DateTime scheduledDate, ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {
            PrepareFiles(scheduledDate, currentAction, parameters);
            var importedPath = Path.Combine(ExcelPath, "Imported");

            if (!Directory.Exists(importedPath))
                Directory.CreateDirectory(importedPath);

            currentAction.Description = string.Format("Looking for file in {0}", ExcelPath);

            if (!Files.Any())
            {
                currentAction.Description = "No files were found.";
                throw new Exception("No files were found.");
            }

            DeletePreviousData(currentAction);

            foreach (var file in Files)
            {
                currentAction.Description = string.Format("{0} found", Path.GetFileName(file));
                currentAction.Description = "Copying data";
                SqlHelper.CopyExcelToSql(string.Format(ExcelConnectionString, file), QueryExcel,
                    TableTemp, DatabaseConnectionString, ExcelColumnMappings, SheetName);

                currentAction.Description = "Moving file to import folder";
                var importedFile = Path.Combine(importedPath, Path.GetFileName(file));

                if (File.Exists(importedFile)) File.Delete(importedFile);
                File.Move(file, importedFile);
            }

            MergeData(currentAction);

            FinishImport(currentAction, parameters);
        }

        public abstract void PrepareFiles(DateTime scheduledDate, ICurrentAction currentAction, Dictionary<string, object> parameters);
        public abstract void FinishImport(ICurrentAction currentAction, Dictionary<string, object> parameters);

        private void MergeData(ICurrentAction currentAction)
        {
            currentAction.Description = "Merging data";
            currentAction.Description = QueryMergeDate;
            SqlHelper.ExecuteNonQuery(QueryMergeDate, DatabaseConnectionString);
        }

        private void DeletePreviousData(ICurrentAction currentAction)
        {
            currentAction.Description = "Deleting Previous Data";
            currentAction.Description = QueryDeletePrevious;
            SqlHelper.ExecuteNonQuery(QueryDeletePrevious, DatabaseConnectionString);
        }

        public void Stop(ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {

        }
    }
}
